// CoreLogicData.do
// Inputs: corelogic`state'_tax.csv, corelogic`state'_deed.csv for many states
// Outputs: corelogic`state'_tax.dta, corelogic`state'_deed.dta for many states
// Date last updated: 1/27/2025

********************************************************************************	
* CoreLogic SQL query used (queried in August 2020)
********************************************************************************
// CoreLogic now combines the tax roll and deed information into Property Basic, so the code we used for this is no longer necessary.

// Original CoreLogic query through RADAR:
	* Paste this code into query builder on Radar Data Warehouse. At the end of each line, we replaced the ``WHERE (state = 'CT')" with the abbreviation for each state. They are all downloaded separately to combine with state-level SLR shape files.
	*https://querytool.kc.frbres.org/radarweb/login.jsp
	*Generates csv files that need importation - get deed and tax data
	
	*SELECT prop_id_dw, state, year_built, adj_gross_sqft, bedrooms_num, totalbaths_num, garage_type, zip_dw, zip, zoning_type, sale_dt, sale_price, mtg_amt, mtg_term, mtg_dt, mtg_loan_type, bldg_sqft, residential_flag, pool_flag, prop_type, fema_panel_num, land_use_type, latitude, longitude, absentee_owner_type, owner_zip, calc_total_value, assd_total_value, tax_amt, tax_year, trans_type, gross_sqft, fips_code, apn, stories_num, effective_year_built, record_dt, owner_corp_flag, multi_apn_flag, sale_code FROM realestate.view_tax_realestate WHERE (state = 'CT')
	*SELECT prop_id_dw, fips_code, mtg_seq_num, cl_dt, cl_seq, doc_num, mail_city, mail_state, mail_zip, sale_price, sale_dt, lender_ln, lender_co_code, lender_zip, mtg_amt, mtg_dt, mtg_int_rate, mtg_code, mtg_int_type, mtg_loan_type, mtg_deed_type, mtg_assumpt_amt, prim_code, deed_code, seller_carry_flag, private_lender_flag, foreclosure_flag, cash_sale_flag, resale_newconst, equity_flag, refi_flag, record_dt, buyer_corp_flag, latitude, longitude FROM realestate.view_deeds_realestate WHERE (state = 'CT')

********************************************************************************	
* Create tax and deed datasets from state
********************************************************************************	
// Create TAX datasets
	foreach state in CT DE FL GA MA MD ME NC NH NJ NY PA RI SC VA{
		import delimited "$pathr\corelogic`state'_tax", stringcols(1 3 4 5 6 7 8 9 11 15 16 17 21 22 26 31 32 34 36 37 38 41) clear 
		save "$pathi\corelogic`state'_tax.dta", replace
	}

// Create DEED datasets
	foreach state in CT DE FL GA MA MD ME NC NH NJ NY PA RI SC VA{
		import delimited "$pathr\corelogic`state'_deed", stringcols(1 3 4 5 6 9 11 12 13 14 16 17 18 19 20 21 23 24 32) clear 
		save "$pathi\corelogic`state'_deed.dta", replace
	}
